07770 338652 | 01737 362752
One of the first projects Complete Codesmiths was ever asked to undertake was the conversion of hundreds of spreadsheets written for the WingZ spreadsheet application on NeXT workstations. (Note: NeXT was the company Steve Jobs founded in 1985 during his hiatus from Apple.) The end-client was a multi-national investment bank, and their traders had created a large portfolio of Wingz spreadsheets which were critical to the smooth operation of the business. The client had made the decision to move to Windows and needed all those spreadsheets replicated within Microsoft Excel.
The spreadsheets themselves were not 'calculate the PV of this future cash-flow' type. They would often retrieve data from the bank’s internal databases and/or third-party data feeds, invoking custom modelling tools to calculate the theoretical values of various financial instruments and publishing bid/ask prices via Reuters.
Whilst the original brief was for a manual conversion of these spreadsheets into the Excel equivalent, it quickly became apparent that automating the task would be a much more sensible approach. Unfortunately, the proprietary Wingz file format was not documented. Undeterred, we begin a process of reverse-engineering the file format - not a simple task.
We began by saving two spreadsheets, one completely empty and one with a single character in cell A1, and then comparing the binary files looking for that single character. By then adding more characters to that same cell, we could work out how variable length strings were handled. By moving the string into cell B1 and then A2 we could work out how rows and columns were handled.
By making further incremental changes and studying the impact on the saved file, we were able to understand the entire file format. Via this process we were able to migrate all the cell contents, formulae and formatting, plus other elements such as graphs and buttons (used to launch macros).
We made no attempt to automatically convert the macros themselves, as the effort required to do so did not seem likely to be justified. However, by removing the burden of the vast majority of the conversion process and thus dramatically increase the rate of conversion. This, in turn, allowed us to spend more time converting the macros themselves.
Unfortunately, the technical environment was not well suited to the task at hand. This resulted in the Wingz Migration Assistant (as the tool was known) being implemented using Visual Basic for Applications with Excel itself. So VBA was used to transfer the original Wingz spreadsheet from the NeXT machine to Windows (via FTP), open the file as a binary stream to parse its contents, and create the equivalent Excel objects.
|
Next: Case Study 2 ⮞ Calling Card Billing and Collection System |